/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package br.com.config.shard;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;

import org.hibernate.shards.util.Lists;

/**
 * A Database utility class to help clean, create and update the in-memory
 * database instances
 *
 * @author Arun Viswanathan
 */
public class DatabaseUtils {

    private static final Iterable<String> CREATE_TABLE_STATEMENTS = Lists.newArrayList(
            "CREATE TABLE hibernate_unique_key (id bigint, next_hi DECIMAL(40,0))", "INSERT INTO hibernate_unique_key(next_hi) VALUES(1)", "CREATE TABLE WEATHER_REPORT (REPORT_ID DECIMAL(40,0) PRIMARY KEY, CONTINENT VARCHAR(50), LATITUDE DECIMAL(10,2), LONGITUDE DECIMAL(10,2), TEMPERATURE DECIMAL(10,0), REPORT_TIME TIMESTAMP)", "CREATE TABLE CUSTOMER (CUSTOMER_ID DECIMAL(40,0) PRIMARY KEY, TENANT_ID VARCHAR(50), FIRST_NAME VARCHAR(50), LAST_NAME VARCHAR(50), LOCATION VARCHAR(50), UPDATE_TIME TIMESTAMP)");
    protected static final List<String> DROP_TABLE_STATEMENTS = Lists.newArrayList(
            "SHUTDOWN" // this deletes the whole database!
            );

    public static Connection createConnection(int index) throws SQLException {
        Connection conn = null;

        try {
            Class.forName("org.postgresql.Driver");
            conn = DriverManager.getConnection("jdbc:postgresql://localhost:5432/shard" + index, "postgres", "postgres");
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }

        return conn;
    }

    public static void destroyDatabase(int index) throws SQLException {
        Connection conn = createConnection(index);
        try {
            for (String statement : DROP_TABLE_STATEMENTS) {
                try {
                    executeUpdate(conn, statement, false);
                } catch (SQLException sqle) {
                    // not interested, keep moving
                }
            }
        } finally {
            conn.close();
        }
    }

    public static void createDatabase(int index) throws SQLException {
        Connection conn = createConnection(index);
        try {
            for (String statement : CREATE_TABLE_STATEMENTS) {
                executeUpdate(conn, statement, false);
            }
        } finally {
            conn.close();
        }
    }

    public static int executeUpdate(
            Connection conn,
            String query,
            boolean closeConnection) throws SQLException {
        Statement statement = null;
        try {
            statement = conn.createStatement();
            return statement.executeUpdate(query);
        } finally {
            if (closeConnection) {
                closeAllResources(statement, conn);
            } else {
                closeAllResources(statement, null);
            }
        }
    }

    public static void closeAllResources(Statement statement, Connection conn) {
        if (conn != null) {
            if (statement != null) {
                try {
                    statement.close();
                } catch (Throwable t) {
                    System.out.println("Error closing statement." + t);
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (Throwable t) {
                    System.out.println("Error closing connection." + t);
                }
            }
        }
    }
}
